package za.ac.wits.elen7045.persistence.util;

import java.util.Date;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import za.ac.wits.elen7045.persistence.model.Account;
import za.ac.wits.elen7045.persistence.model.AccountType;
import za.ac.wits.elen7045.persistence.model.Credential;
import za.ac.wits.elen7045.persistence.model.CreditCardProviderStatement;
import za.ac.wits.elen7045.persistence.model.MunicipalityStatement;
import za.ac.wits.elen7045.persistence.model.ServiceProvider;
import za.ac.wits.elen7045.persistence.model.ServiceProviderName;
import za.ac.wits.elen7045.persistence.model.Statement;
import za.ac.wits.elen7045.persistence.model.TelcoServiceProviderStatement;
import za.ac.wits.elen7045.persistence.model.User;

public abstract class SQLQueries implements UserTableConstants, AccountTableConstants, CredentialsTableConstants, ServiceProviderTableConstants, StatementTableConstants, 
											MunicipalityStatementTableConstants, CreditCardProviderStatementTableConstants, TelcoServiceProviderStatementTableConstants {

	/* ------------------------------------------------------------ *
	 * 							USERS								*
	 * ------------------------------------------------------------ */
	
	public static String getLatestPasswords() {
		return 	"SELECT "+ USER_ID +", MAX("+ PASSWORD_CREATION_DATE +"), "+ PASSWORD +" AS "+ USER_LATEST_PASSWORD +" "+
				"FROM "+ USER_PASSWORDS_TABLE +" "+
				"GROUP BY "+ USER_ID;
	}
	
	public static String isUserAuthorised(String username, String password) {
		return 	"SELECT u.*, p."+ USER_LATEST_PASSWORD +" "+
				"FROM "+ USERS_TABLE +" AS u, ("+ getLatestPasswords() +") AS p "+
				"WHERE u."+ USER_ID +"=p."+ USER_ID +" AND u."+ USER_USERNAME +"='"+ username +"' " +
				"AND p."+ USER_LATEST_PASSWORD +"='"+ password +"'";
	}
	
	public static String getUser(String username) {
		return	"SELECT u.*, p."+ USER_LATEST_PASSWORD +" "+
				"FROM "+ USERS_TABLE +" AS u, ("+ getLatestPasswords() +") AS p "+
				"WHERE u."+ USER_ID +"=p."+ USER_ID +" AND u."+ USER_USERNAME +"='"+ username +"'";
	}
	
	public static String getUserWithoutPassword(String username) {
		return	"SELECT u.*, '' AS "+ USER_LATEST_PASSWORD +" "+
				"FROM "+ USERS_TABLE +" AS u "+
				"WHERE "+ USER_USERNAME +"='"+ username +"'";
	}
	
	public static String getAllUsers() {
		return	"SELECT u.*, p."+ USER_LATEST_PASSWORD +" "+
				"FROM "+ USERS_TABLE +" AS u, ("+ getLatestPasswords() +") AS p " +
				"WHERE u."+ USER_ID +"=p."+ USER_ID;
	}
	
	public static String addUser(User user) {
		return	"INSERT INTO "+ USERS_TABLE +" ("+ USER_NAME +", "+ USER_USERNAME +") "+
				"VALUES ('"+ user.getName() +"', '"+ user.getUsername() +"')";
	}
	
	public static String addUserPassword(User user) {
		String currentDate = DateUtilities.getLongDateAsString(new Date()); 
		return	"INSERT INTO "+ USER_PASSWORDS_TABLE +" ("+ USER_ID +", "+ PASSWORD_CREATION_DATE +", "+ PASSWORD +") "+
				"VALUES ("+ user.getId() +", '"+ currentDate +"', '"+ user.getPassword() +"')";
	}
	
	public static String removeUser(User user) {
		return	"DELETE FROM "+ USERS_TABLE +" WHERE "+ USER_ID +"="+ user.getId();
	}
	
	public static String removeUserPasswords(User user) {
		return	"DELETE FROM "+ USER_PASSWORDS_TABLE +" WHERE "+ USER_ID +"="+ user.getId();
	}
	
	
	/* ------------------------------------------------------------ *
	 * 						SERVICE PROVIDERS						*
	 * ------------------------------------------------------------ */
	
	public static String getServiceProvider(ServiceProvider serviceProvider) {
		return	"SELECT * "+
				"FROM "+ SERVICE_PROVIDERS_TABLE +" "+
				"WHERE "+ SERVICE_PROVIDER_TYPE +"='"+ serviceProvider.getServiceProviderType().getName() +"' " +
				"AND "+ SERVICE_PROVIDER_NAME +"='"+ serviceProvider.getName() +"' " +
				"AND "+ SERVICE_PROVIDER_URL +"='"+ serviceProvider.getUrl() +"'";
	}
	
	public static String getServiceProvider(Account account) {
		return	getServiceProvider(account.getServiceProviderType());
	}
	
	public static String getServiceProvider(ServiceProviderName serviceProviderType) {
		return	"SELECT * "+
				"FROM "+ SERVICE_PROVIDERS_TABLE +"  "+
				"WHERE "+ SERVICE_PROVIDER_TYPE +"='"+ serviceProviderType.getName() +"'";
	}
	
	public static String getServiceProviders() {
		return	"SELECT * FROM "+ SERVICE_PROVIDERS_TABLE;
	}
	
	public static String getRequiredCredentials(ServiceProvider serviceProvider) {
		return	"SELECT * "+
				"FROM "+ SERV_PROV_CRED_TABLE +" "+
				"WHERE "+ SERVICE_PROVIDER_ID +"="+ serviceProvider.getId();
	}
	
	public static String serviceProviderExist(ServiceProvider serviceProvider) {
		return	"SELECT * " +
				"FROM "+ SERVICE_PROVIDERS_TABLE +" "+
				"WHERE "+ SERVICE_PROVIDER_TYPE +"='"+ serviceProvider.getServiceProviderType().getName() +"' " +
				"AND "+ SERVICE_PROVIDER_NAME +"='"+ serviceProvider.getName() +"' "+
				"AND "+ SERVICE_PROVIDER_URL +"='"+ serviceProvider.getUrl() +"'";
	}
	
	public static String addServiceProvider(ServiceProvider serviceProvider) {
		return	"INSERT INTO "+ SERVICE_PROVIDERS_TABLE +" ("+ SERVICE_PROVIDER_TYPE +","+ SERVICE_PROVIDER_NAME +","+ SERVICE_PROVIDER_URL +") " +
				"VALUES ('"+ serviceProvider.getServiceProviderType().getName() +"','"+ serviceProvider.getName() +"','"+ serviceProvider.getUrl() +"')";
	}
	
	public static String removeServiceProvider(ServiceProvider serviceProvider) {
		return	"DELETE FROM "+ SERVICE_PROVIDERS_TABLE +" " +
				"WHERE "+ SERVICE_PROVIDER_ID +"="+ serviceProvider.getId();
	}
	
	public static String requiredCredentialExist(ServiceProvider serviceProvider, Credential credential) {
		return	"SELECT * "+
				"FROM "+ SERV_PROV_CRED_TABLE +" "+
				"WHERE "+ SERVICE_PROVIDER_ID +"="+ serviceProvider.getId() +" AND "+ CREDENTIAL_NAME +"='"+ credential.getName() +"'";
	}
	
	public static String addRequiredCredential(ServiceProvider serviceProvider, Credential credential) {
		return	"INSERT INTO "+ SERV_PROV_CRED_TABLE +" ("+ SERVICE_PROVIDER_ID +", "+ CREDENTIAL_NAME +") "+
				"VALUES ("+ serviceProvider.getId() +", '"+ credential.getName() +"')";
	}
	
	public static String removeRequiredCredential(ServiceProvider serviceProvider, Credential credential) {
		return	"DELETE FROM "+ SERV_PROV_CRED_TABLE +" "+
				"WHERE "+ SERVICE_PROVIDER_ID +"="+ serviceProvider.getId() +" AND "+ CREDENTIAL_NAME +"='"+ credential.getName() +"'";
	}
	
	
	/* ------------------------------------------------------------ *
	 * 							ACCOUNTS							*
	 * ------------------------------------------------------------ */
	
	public static String getAccount(Account account) {
		return	"SELECT * "+
				"FROM "+ ACCOUNTS_TABLE +" "+
				"WHERE "+ SERVICE_PROVIDER_TYPE +"='"+ account.getServiceProviderType().getName() +"' " +
				"AND "+ ACCOUNT_NUMBER +"="+ account.getAccountNumber() +" " +
				"AND "+ ACCOUNT_OWNER_NAME +"='"+ account.getAccountOwnerName() +"'";
	}
	
	public static String getAccount(User user, Account account) {
		return	"SELECT a.* "+
				"FROM "+ ACCOUNTS_TABLE +" AS a, "+ USER_ACCOUNTS_TABLE +" AS ua "+
				"WHERE a."+ ACCOUNT_ID +"=ua."+ ACCOUNT_ID +" AND ua."+ USER_ID +"="+ user.getId() +" AND "+ ACCOUNT_NUMBER +"="+ account.getAccountNumber();
	}
	
	public static String getAccounts(User user) {
		return	"SELECT a.* "+
				"FROM "+ ACCOUNTS_TABLE +" AS a, "+ USER_ACCOUNTS_TABLE +" AS ua "+
				"WHERE a."+ ACCOUNT_ID +"=ua."+ ACCOUNT_ID +" AND ua."+ USER_ID +"="+ user.getId();
	}
	
	public static String getAccounts(ServiceProviderName serviceProviderType) {
		return	"SELECT * "+
				"FROM "+ ACCOUNTS_TABLE +" "+
				"WHERE "+ SERVICE_PROVIDER_TYPE +"='"+ serviceProviderType.getName() +"'";
	}
	
	public static String getAccountCredential(Account account, String name) {
		return	"SELECT * "+
				"FROM "+ ACC_CRED_TABLE +" "+
				"WHERE "+ ACCOUNT_ID +"="+ account.getId() +" AND "+ CREDENTIAL_NAME +"='"+ name +"'";
	}
	
	public static String getAccountCredentials(Account account) {
		return	"SELECT * "+
				"FROM "+ ACC_CRED_TABLE +" "+
				"WHERE "+ ACCOUNT_ID +"="+ account.getId();
	}
	
	public static String getAccountCredentials(Account account, boolean encrypted) {
		return	"SELECT * "+
				"FROM "+ ACC_CRED_TABLE +" "+
				"WHERE "+ ACCOUNT_ID +"="+ account.getId() +" AND "+ CREDENTIAL_ENCRYPTED +"='"+ encrypted +"'";
	}
	
	public static String accountExist(Account account) {
		return	"SELECT * " +
				"FROM "+ ACCOUNTS_TABLE +" "+
				"WHERE "+ SERVICE_PROVIDER_TYPE +"='"+ account.getServiceProviderType().getName() +"' " +
				"AND "+ ACCOUNT_NUMBER +"="+ account.getAccountNumber() +" "+
				"AND "+ ACCOUNT_OWNER_NAME +"='"+ account.getAccountOwnerName() +"'";
	}
	
	public static String accountExist(User user, Account account) {
		return	"SELECT a.* " +
				"FROM "+ ACCOUNTS_TABLE +" AS a, "+ USER_ACCOUNTS_TABLE +" AS ua" +" "+
				"WHERE a."+ ACCOUNT_ID +"=ua."+ ACCOUNT_ID +" AND ua."+ ACCOUNT_ID +"="+ account.getId() +" "+
				"AND ua."+ USER_ID +"="+ user.getId();
	}
	
	public static String addAccount(Account account) {
		return	"INSERT INTO "+ ACCOUNTS_TABLE +" ("+ SERVICE_PROVIDER_TYPE +","+ ACCOUNT_NUMBER +","+ ACCOUNT_OWNER_NAME +") " +
				   "VALUES ('"+ account.getServiceProviderType().getName() +"',"+ account.getAccountNumber() +",'"+ account.getAccountOwnerName() +"')";
	}
	
	public static String addUserAccountReference(User user, Account account) {
		return	"INSERT INTO "+ USER_ACCOUNTS_TABLE +" ("+ USER_ID +", "+ ACCOUNT_ID +") "+
				"VALUES ("+ user.getId() +", "+ account.getId() +")";
	}

	public static String removeAccount(Account account) {
		return	"DELETE FROM "+ ACCOUNTS_TABLE +" WHERE "+ ACCOUNT_ID +"="+ account.getId();
	}
	
	public static String removeUserAccountReference(User user, Account account) {
		return	"DELETE FROM "+ USER_ACCOUNTS_TABLE +" WHERE "+ USER_ID +"="+ user.getId() +" AND "+ ACCOUNT_ID +"="+ account.getId();
	}
	
	public static String accountCredentialExist(Account account, Credential credential) {
		return	"SELECT * "+
				"FROM "+ ACC_CRED_TABLE +" "+
				"WHERE "+ ACCOUNT_ID +"="+ account.getId() +" AND "+ CREDENTIAL_NAME +"='"+ credential.getName() +"'";
	}
	
	public static String addAccountCredential(Credential credential) {
		return	"INSERT INTO "+ ACC_CRED_TABLE +" ("+ ACCOUNT_ID +", "+ CREDENTIAL_NAME +", "+ CREDENTIAL_VALUE +", "+ CREDENTIAL_ENCRYPTED +") "+
				"VALUES ("+ credential.getOwnerId() +", '"+ credential.getName() +"', '"+ credential.getValue(false) +"', '"+ Boolean.toString(credential.isEncrypted()) +"')";
	}
	
	public static String removeAccountCredential(Credential credential) {
		return	"DELETE FROM "+ ACC_CRED_TABLE +" "+
				"WHERE "+ ACCOUNT_ID +"="+ credential.getOwnerId();
	}
	
	
	/* ------------------------------------------------------------ *
	 * 							STATEMENTS							*
	 * ------------------------------------------------------------ */
	
	public static String getLatestAccountStatement(Account account) {
		return	"SELECT *, MAX("+ STATEMENT_ID +") "+
				"FROM "+ STATEMENTS_TABLE +" WHERE "+ ACCOUNT_ID +"="+ account.getId() +" " +
				"GROUP BY "+ ACCOUNT_ID;
	}
	
	public static String getAccountStatement(Account account, Statement statement) {
		return	"SELECT * " +
				"FROM "+ STATEMENTS_TABLE +" "+
				"WHERE "+ ACCOUNT_ID +"="+ account.getId() +" AND "+ STATEMENT_NUMBER +"="+ statement.getStatementNumber() +" AND "+ STATEMENT_DATE +"='"+ statement.getStatementDate() +"'";
	}
	
	public static String getAccountStatements(Account account, Date date) {
		return	"SELECT * " +
				"FROM "+ STATEMENTS_TABLE +" "+
				"WHERE "+ ACCOUNT_ID +"="+ account.getId() +" AND "+ STATEMENT_DATE +"="+ getDateAsSQLString(date);
	}
	
	public static String getAccountStatementsBefore(Account account, Date date) {
		return	"SELECT * " +
				"FROM "+ STATEMENTS_TABLE +" "+
				"WHERE "+ ACCOUNT_ID +"="+ account.getId() +" AND "+ STATEMENT_DATE +" < "+ getDateAsSQLString(date);
	}
	
	public static String getAccountStatementsAfter(Account account, Date date) {
		return	"SELECT * " +
				"FROM "+ STATEMENTS_TABLE +" "+
				"WHERE "+ ACCOUNT_ID +"="+ account.getId() +" AND "+ STATEMENT_DATE +" > "+ getDateAsSQLString(date);
	}
	
	public static String getAccountStatements(Account account) {
		return	"SELECT * " +
				"FROM "+ STATEMENTS_TABLE +" "+
				"WHERE "+ ACCOUNT_ID +"="+ account.getId();
	}
	
	public static String statementExist(Statement statement) {
		return	"SELECT * " +
				"FROM "+ STATEMENTS_TABLE +" "+
				"WHERE "+ ACCOUNT_ID +"="+ statement.getAccountId() +" " +
				"AND "+ STATEMENT_NUMBER +"="+ statement.getStatementNumber() +" "+
				"AND "+ STATEMENT_DATE +"='"+ statement.getStatementDate() +"'";
	}
	
	public static String municipalityStatementExist(Statement statement) {
		return	"SELECT * " +
				"FROM "+ MUNICIPALITY_STATEMENTS_TABLE +" "+
				"WHERE "+ STATEMENT_ID +"="+ statement.getId();
	}
	
	public static String creditCardProviderStatementExist(Statement statement) {
		return	"SELECT * " +
				"FROM "+ CC_PROVIDER_STATEMENTS_TABLE +" "+
				"WHERE "+ STATEMENT_ID +"="+ statement.getId();
	}
	
	public static String telcoServiceProviderStatementExist(Statement statement) {
		return	"SELECT * " +
				"FROM "+ TELCO_SERVICE_PROVIDER_STATEMENTS_TABLE +" "+
				"WHERE "+ STATEMENT_ID +"="+ statement.getId();
	}
	
	public static String addAccountStatement(Statement statement) {
		Map<String, String> map = getStatementValuePairs(AccountType.OTHER, statement);
		/* create delimited strings for value-pairs */
		int index=0;
		int size = map.size();
		StringBuffer nameBuffer = new StringBuffer();
		StringBuffer valueBuffer = new StringBuffer();
		for (String name : map.keySet()) {
			nameBuffer.append(name).append(index < size-1 ? "," : "");
			valueBuffer.append(map.get(name)).append(index < size-1 ? "," : "");
			index++;
		}
		return	"INSERT INTO "+ STATEMENTS_TABLE +" ("+ nameBuffer.toString() +") "+
				"VALUES ("+ valueBuffer.toString() +")";
	}
	
	public static String removeAccountStatement(Statement statement) {
		return	"DELETE FROM "+ STATEMENTS_TABLE +" WHERE "+ STATEMENT_ID +"="+ statement.getId();
	}
	
	/* Municipality Statement */
	
	public static String getMunicipalityStatement(Statement statement) {
		List<String> columns = getQueryColumnNames(STATEMENTS_TABLE, "s");
		columns.addAll(getQueryColumnNames(MUNICIPALITY_STATEMENTS_TABLE, "m"));
		return	"SELECT "+ getListAsDelimitedString(columns, ",") +" "+
				"FROM "+ STATEMENTS_TABLE +" s, "+ MUNICIPALITY_STATEMENTS_TABLE +" m " +
				"WHERE s."+ STATEMENT_ID +"=m."+ STATEMENT_ID +" AND s."+ STATEMENT_ID +"="+ statement.getId();
	}
	
	public static String addMunicipalityStatement(MunicipalityStatement statement) {
		Map<String, String> map = getStatementValuePairs(AccountType.MUNICIPALITY, statement);
		/* create delimited strings for value-pairs */
		int index=0;
		int size = map.size();
		StringBuffer nameBuffer = new StringBuffer();
		StringBuffer valueBuffer = new StringBuffer();
		for (String name : map.keySet()) {
			nameBuffer.append(name).append(index < size-1 ? "," : "");
			valueBuffer.append(map.get(name)).append(index < size-1 ? "," : "");
			index++;
		}
		return	"INSERT INTO "+ MUNICIPALITY_STATEMENTS_TABLE +" ("+ nameBuffer.toString() +") "+
				"VALUES ("+ valueBuffer.toString() +")";
	}
	
	public static String removeMunicipalityStatement(MunicipalityStatement statement) {
		return	"DELETE FROM "+ MUNICIPALITY_STATEMENTS_TABLE +" WHERE "+ STATEMENT_ID +"="+ statement.getId();
	}
	
	/* Credit Card Provider Statement */

	public static String getCreditCardProviderStatement(Statement statement) {
		List<String> columns = getQueryColumnNames(STATEMENTS_TABLE, "s");
		columns.addAll(getQueryColumnNames(CC_PROVIDER_STATEMENTS_TABLE, "c"));
		return	"SELECT "+ getListAsDelimitedString(columns, ",") +" "+
				"FROM "+ STATEMENTS_TABLE +" AS s, "+ CC_PROVIDER_STATEMENTS_TABLE +" AS c " +
				"WHERE s."+ STATEMENT_ID +"=c."+ STATEMENT_ID +" AND s."+ STATEMENT_ID +"="+ statement.getId();
	}
	
	public static String addCreditCardProviderStatement(CreditCardProviderStatement statement) {
		Map<String, String> map = getStatementValuePairs(AccountType.CREDIT_CARD_PROVIDER, statement);
		/* create delimited strings for value-pairs */
		int index=0;
		int size = map.size();
		StringBuffer nameBuffer = new StringBuffer();
		StringBuffer valueBuffer = new StringBuffer();
		for (String name : map.keySet()) {
			nameBuffer.append(name).append(index < size-1 ? "," : "");
			valueBuffer.append(map.get(name)).append(index < size-1 ? "," : "");
			index++;
		}
		return	"INSERT INTO "+ CC_PROVIDER_STATEMENTS_TABLE +" ("+ nameBuffer.toString() +") "+
				"VALUES ("+ valueBuffer.toString() +")";
	}
	
	public static String removeCreditCardProviderStatement(CreditCardProviderStatement statement) {
		return	"DELETE FROM "+ CC_PROVIDER_STATEMENTS_TABLE +" WHERE "+ STATEMENT_ID +"="+ statement.getId();
	}
	
	/* Credit Card Provider Statement */
	
	public static String getTelcoServiceProviderStatement(Statement statement) {
		List<String> columns = getQueryColumnNames(STATEMENTS_TABLE, "s");
		columns.addAll(getQueryColumnNames(TELCO_SERVICE_PROVIDER_STATEMENTS_TABLE, "t"));
		return	"SELECT "+ getListAsDelimitedString(columns, ",") +" "+
				"FROM "+ STATEMENTS_TABLE +" AS s, "+ TELCO_SERVICE_PROVIDER_STATEMENTS_TABLE +" AS t " +
				"WHERE s."+ STATEMENT_ID +"=t."+ STATEMENT_ID +" AND s."+ STATEMENT_ID +"="+ statement.getId();
	}
	
	public static String addTelcoServiceProviderStatement(TelcoServiceProviderStatement statement) {
		Map<String, String> map = getStatementValuePairs(AccountType.TELCO_SERVICE_PROVIDER, statement);
		/* create delimited strings for value-pairs */
		int index=0;
		int size = map.size();
		StringBuffer nameBuffer = new StringBuffer();
		StringBuffer valueBuffer = new StringBuffer();
		for (String name : map.keySet()) {
			nameBuffer.append(name).append(index < size-1 ? "," : "");
			valueBuffer.append(map.get(name)).append(index < size-1 ? "," : "");
			index++;
		}
		return	"INSERT INTO "+ TELCO_SERVICE_PROVIDER_STATEMENTS_TABLE +" ("+ nameBuffer.toString() +") "+
				"VALUES ("+ valueBuffer.toString() +")";
	}
	
	public static String removeTelcoServiceProviderStatement(TelcoServiceProviderStatement statement) {
		return	"DELETE FROM "+ TELCO_SERVICE_PROVIDER_STATEMENTS_TABLE +" WHERE "+ STATEMENT_ID +"="+ statement.getId();
	}
	
	/* Utilities */
	
	private static String getDateAsSQLString(Date date) {
		return "'"+ DateUtilities.getLongDateAsString(date) +"'";
	}
	
	private static Map<String, String> getStatementValuePairs(AccountType accountType, Statement stmnt) {
		Map<String, String> values = new LinkedHashMap<String, String>();
		if (accountType == AccountType.MUNICIPALITY) {
			MunicipalityStatement statement = (MunicipalityStatement) stmnt;
			values.put(STATEMENT_ID, Integer.toString(stmnt.getId()));
			values.put(INSTALMENT_NOTICE, "'"+ statement.getInstalmentNotice() +"'");
			values.put(ELECTRICITY_USED, Integer.toString(statement.getElectricityUsed()));
			values.put(ELECTRICITY_CHARGES, Integer.toString(statement.getElectricityCharges()));
			values.put(GAS_USED, Integer.toString(statement.getGasUsed()));
			values.put(GAS_CHARGES, Integer.toString(statement.getGasCharges()));
			values.put(WATER_USED, Integer.toString(statement.getWaterUsed()));
			values.put(WATER_CHARGES, Integer.toString(statement.getWaterCharges()));
			values.put(SEWERAGE_CHARGES, Integer.toString(statement.getSewerageCharges()));
			values.put(REFUSE_CHARGES, Integer.toString(statement.getRefuseCharges()));
		} else if (accountType == AccountType.CREDIT_CARD_PROVIDER) {
			CreditCardProviderStatement statement = (CreditCardProviderStatement) stmnt;
			values.put(STATEMENT_ID, Integer.toString(stmnt.getId()));
			values.put(CARD_TYPE, "'"+ statement.getCardType().getName() +"'");
			values.put(INTEREST_RATE, Double.toString(statement.getInterestRate()));
			values.put(CREDIT_LIMIT, Integer.toString(statement.getCreditLimit()));
			values.put(CREDIT_AVAILABLE, Integer.toString(statement.getCreditAvailable()));
			values.put(MIN_AMOUNT_DUE, Integer.toString(statement.getMinAmountDue()));
		} else if (accountType == AccountType.TELCO_SERVICE_PROVIDER) {
			TelcoServiceProviderStatement statement = (TelcoServiceProviderStatement) stmnt;
			values.put(STATEMENT_ID, Integer.toString(stmnt.getId()));
			values.put(TELEPHONE_NUMBER, Integer.toString(statement.getTelephoneNumber()));
			values.put(SERVICE_CHARGES, Double.toString(statement.getServicesCharges()));
			values.put(CALL_CHARGES, Double.toString(statement.getCallCharges()));
			values.put(TOTAL_CALLS, Integer.toString(statement.getTotalCalls()));
			values.put(TOTAL_CALL_DURATION, Integer.toString(statement.getTotalCallDuration()));
		} else {
			values.put(ACCOUNT_ID, Integer.toString(stmnt.getAccountId()));
			values.put(STATEMENT_NUMBER, Integer.toString(stmnt.getStatementNumber()));
			values.put(STATEMENT_DATE, "'"+ stmnt.getStatementDate() +"'");
			values.put(TOTAL_DUE, Integer.toString(stmnt.getAmountDue()));
			if (stmnt.getAmountDueDate() != null) {
				values.put(DUE_DATE, "'"+ stmnt.getAmountDueDate() +"'");
			}
			values.put(OPENING_BALANCE, Integer.toString(stmnt.getOpenBalance()));
			values.put(CLOSING_BALANCE, Integer.toString(stmnt.getCloseBalance()));
			values.put(PAYMENT_RECEIVED, Integer.toString(stmnt.getPaymentReceived()));
			values.put(NEW_CHARGES, Integer.toString(stmnt.getNewCharges()));
			values.put(DEDUCTIONS, Integer.toString(stmnt.getDeductions()));
			values.put(DISCOUNT, Integer.toString(stmnt.getDiscount()));
			values.put(VAT, Integer.toString(stmnt.getVat()));
		}
		return values;
	}
	
	private static List<String> getQueryColumnNames(String tableName, String tableReference) {
		String ref = tableReference == null ? tableName +"." : tableReference +".";
		List<String> columnNames = new LinkedList<String>();
		if (tableName.equals(STATEMENTS_TABLE)) {
			columnNames.add(ref + ACCOUNT_ID);
			columnNames.add(ref + STATEMENT_ID);
			columnNames.add(ref + STATEMENT_NUMBER);
			columnNames.add(ref + STATEMENT_DATE);
			columnNames.add(ref + TOTAL_DUE);
			columnNames.add(ref + DUE_DATE);
			columnNames.add(ref + OPENING_BALANCE);
			columnNames.add(ref + CLOSING_BALANCE);
			columnNames.add(ref + PAYMENT_RECEIVED);
			columnNames.add(ref + NEW_CHARGES);
			columnNames.add(ref + DEDUCTIONS);
			columnNames.add(ref + DISCOUNT);
			columnNames.add(ref + VAT);
		} else if (tableName.equals(MUNICIPALITY_STATEMENTS_TABLE)) {
			columnNames.add(ref + INSTALMENT_NOTICE);
			columnNames.add(ref + ELECTRICITY_USED);
			columnNames.add(ref + ELECTRICITY_CHARGES);
			columnNames.add(ref + GAS_USED);
			columnNames.add(ref + GAS_CHARGES);
			columnNames.add(ref + WATER_USED);
			columnNames.add(ref + WATER_CHARGES);
			columnNames.add(ref + SEWERAGE_CHARGES);
			columnNames.add(ref + REFUSE_CHARGES);
		} else if (tableName.equals(CC_PROVIDER_STATEMENTS_TABLE)) {
			columnNames.add(ref + CARD_TYPE);
			columnNames.add(ref + INTEREST_RATE);
			columnNames.add(ref + CREDIT_LIMIT);
			columnNames.add(ref + CREDIT_AVAILABLE);
			columnNames.add(ref + MIN_AMOUNT_DUE);
		} else if (tableName.equals(TELCO_SERVICE_PROVIDER_STATEMENTS_TABLE)) {
			columnNames.add(ref + TELEPHONE_NUMBER);
			columnNames.add(ref + SERVICE_CHARGES);
			columnNames.add(ref + CALL_CHARGES);
			columnNames.add(ref + TOTAL_CALLS);
			columnNames.add(ref + TOTAL_CALL_DURATION);
		}
		return columnNames;
	}
	
	private static String getListAsDelimitedString(List<String> values, String delimiter) {
		StringBuffer buffer = new StringBuffer();
		int size = values.size();
		for (int index=0; index < size; index++) {
			buffer.append(values.get(index));
			buffer.append(index < size-1 ? delimiter : "");
		}
		return buffer.toString();
	}
	
}
